package com.qzxy.premiumhotelsystem.mapper;

import com.qzxy.premiumhotelsystem.domain.dto.GuestAndVipDTO;
import com.qzxy.premiumhotelsystem.domain.dto.GuestDTO;
import com.qzxy.premiumhotelsystem.domain.entity.Admin;
import com.qzxy.premiumhotelsystem.domain.entity.Employees;
import com.qzxy.premiumhotelsystem.domain.entity.Guests;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qzxy.premiumhotelsystem.domain.vo.GuestInfoVo;
import com.qzxy.premiumhotelsystem.domain.vo.LoginVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author 14912
 * @description 针对表【guests】的数据库操作Mapper
 * @createDate 2024-09-12 15:55:14
 * @Entity com.qzxy.premiumhotelsystem.domain.pojo.Guests
 */
@Mapper
public interface GuestsMapper extends BaseMapper<Guests> {

    /**
     * 用户登录方法
     *
     * @param loginVo
     * @return
     */
    @Select("select password from guests where telephone = #{telephone}")
    String login(LoginVo loginVo);

    /**
     * 根据手机号删除用户
     *
     * @param telephone
     * @return
     */
    @Delete("delete * from guests where telephone = #{telephone}")
    boolean deleteByTelephoneBoolean(String telephone);

    /**
     * 通过电话查找顾客
     *
     * @param telephone
     * @return
     */
    @Select("select * from guests where telephone = #{telephone}")
    Guests getByTelephone(String telephone);

    /**
     * 通过邮箱查看顾客
     *
     * @param email
     * @return
     */
    @Select("select * from guests where email = #{email}")
    Guests getByEmail(String email);

    /**
     * 查询所有顾客
     *
     * @return
     */
    @Select("select * from guests")
    List<Guests> allGuests();

    /**
     * 更新登录状态
     *
     * @param loginIp
     * @param loginTime
     * @return
     */
    @Update("update guests set login_ip = #{loginIp},login_time = #{loginTime} where telephone = #{telephone}")
    boolean updateLoginStatus(String loginIp, String loginTime, String telephone);

    /**
     * 修改顾客基本信息
     *
     * @param guestInfoVo
     * @return
     */
    @Update("update guests set " +
            "nickname = #{nickname}," +
            "id_card = #{idCard}, " +
            "age = #{age}," +
//            "img_Url = #{imgUrl}," +
            "sex = #{sex} " +
            "where telephone = #{telephone}")
    boolean updateInformation(GuestInfoVo guestInfoVo);

    /**
     * 头像上传
     *
     * @param url
     * @param telephone
     * @return
     */
    @Update("update guests set img_Url = #{url} where telephone = #{telephone}")
    boolean uploadAvatar(String url, String telephone);

    /**
     * 获取会员类型
     * @param telephone
     * @return
     */
    @Select("select guests.*,vip.vip_type_id from guests " +
            "left join vip " +
            "on guests.guest_id = vip.guest_id " +
            "where guests.telephone = #{telephone}")
    GuestAndVipDTO getVipType(String telephone);

    /**
     * 更新用户全部信息
     *
     * @param guests
     * @return
     */
    @Update("update guests set " +
            "nickname = #{nickname}," +
            "id_card = #{idCard}, " +
            "age = #{age}," +
            "img_Url = #{imgUrl}," +
            "sex = #{sex}, " +
            "pay_pwd = #{payPwd}," +
            "balance = #{balance} " +
            "where telephone = #{telephone}")
    boolean updateAllInformation(Guests guests);

    @Select("select a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
            "a.age,a.img_url,a.sex,b.order_id,b.room_id,c.room_number,b.duration_day," +
            "b.check_in_time,b.check_out_time,b.status,d.vip_type_id,e.vip_type from guests a " +
            "left join orders b " +
            "on a.guest_id = b.guest_id " +
            "left join rooms c " +
            "on b.room_id = c.room_id " +
            "left join vip d " +
            "on a.guest_id = d.guest_id " +
            "left join vip_types e " +
            "on d.vip_type_id = e.vip_type_id ")
    List<GuestDTO> guestsForOrderStatusAll();

    @Select("select distinct a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
            "a.age,a.img_url,a.sex,b.order_id,b.room_id,c.room_number,b.duration_day," +
            "b.check_in_time,b.check_out_time,b.status,d.vip_type_id,e.vip_type from guests a " +
            "left join orders b " +
            "on a.guest_id = b.guest_id " +
            "left join rooms c " +
            "on b.room_id = c.room_id " +
            "left join vip d " +
            "on a.guest_id = d.guest_id " +
            "left join vip_types e " +
            "on d.vip_type_id = e.vip_type_id " +
            "where b.status='已支付' or b.status = '已入住' or b.status = '已续订' " +
            "ORDER BY b.status DESC ,d.vip_type_id ASC ")
    List<GuestDTO> guestsForOrderStatus();
//    不大于一天
    @Select("select a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
            "a.age,a.img_url,a.sex,b.order_id,b.room_id,c.room_number,b.duration_day," +
            "b.check_in_time,b.check_out_time,b.status,d.vip_type_id,e.vip_type from guests a " +
            "left join orders b " +
            "on a.guest_id = b.guest_id " +
            "left join rooms c " +
            "on b.room_id = c.room_id " +
            "left join vip d " +
            "on a.guest_id = d.guest_id " +
            "left join vip_types e " +
            "on d.vip_type_id = e.vip_type_id " +
            "where b.status='已完成' " +
            "ORDER BY b.check_out_time DESC,d.vip_type_id ASC")
    List<GuestDTO> guestsForOrderStatusHistory();

//    大于一天
//    @Select("select a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
//            "a.age,a.img_url,a.sex,b.order_id,b.room_id,c.room_number,b.duration_day," +
//            "b.check_in_time,b.check_out_time,b.status,d.vip_type_id,e.vip_type from guests a " +
//            "left join orders b " +
//            "on a.guest_id = b.guest_id " +
//            "left join rooms c " +
//            "on b.room_id = c.room_id " +
//            "left join vip d " +
//            "on a.guest_id = d.guest_id " +
//            "left join vip_types e " +
//            "on d.vip_type_id = e.vip_type_id " +
//            "where b.status='已完成' and NOW() > b.check_out_time " +
//            "ORDER BY b.check_out_time DESC,d.vip_type_id ASC")
//    List<GuestDTO> guestsForOrderStatusHistory();


//    @Select("select a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
//            "a.age,a.img_url,a.sex,b.order_id,b.room_id,b.duration_day," +
//            "b.check_in_time,b.check_out_time,c.status,d.vip_type_id from guests a " +
//            "left join orders b " +
//            "on a.guest_id = b.guest_id " +
//            "left join rooms c " +
//            "on b.room_id = c.room_id " +
//            "left join vip d " +
//            "on a.guest_id = d.guest_id ")
//    List<GuestDTO> guestsForOrderStatusAll();
//
//    @Select("select a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
//            "a.age,a.img_url,a.sex,b.order_id,b.room_id,b.duration_day," +
//            "b.check_in_time,b.check_out_time,c.status,d.vip_type_id from guests a " +
//            "left join orders b " +
//            "on a.guest_id = b.guest_id " +
//            "left join rooms c " +
//            "on b.room_id = c.room_id " +
//            "left join vip d " +
//            "on a.guest_id = d.guest_id " +
//            "where c.status='已入住' or c.status = '已预订' " +
//            "ORDER BY c.status DESC ,d.vip_type_id ASC ")
//    List<GuestDTO> guestsForOrderStatus();
//
//    @Select("select a.guest_id,a.email,a.telephone,a.nickname,a.id_card," +
//            "a.age,a.img_url,a.sex,b.order_id,b.room_id,b.duration_day," +
//            "b.check_in_time,b.check_out_time,c.status,d.vip_type_id from guests a " +
//            "left join orders b " +
//            "on a.guest_id = b.guest_id " +
//            "left join rooms c " +
//            "on b.room_id = c.room_id " +
//            "left join vip d " +
//            "on a.guest_id = d.guest_id " +
//            "where b.status='已支付' and NOW() > b.check_out_time " +
//            "ORDER BY b.check_out_time,d.vip_type_id ASC")
//    List<GuestDTO> guestsForOrderStatusHistory();
}




